#!/usr/bin/python3
# -*- coding:utf-8 -*-
# __author__ = '__Jack__'
import re
from datetime import datetime
from lib2to3.pygram import pattern_grammar
from urllib.parse import urlencode

from fastapi import APIRouter, Depends, HTTPException, Body
from fastapi.params import Query
from fastapi.responses import JSONResponse, Response
from orjson import orjson
from sqlalchemy import select, delete, desc, func
from starlette.responses import RedirectResponse

from config.get_db import get_db_sync as get_db
from config.get_db import get_db as get_db_async
from sqlalchemy.orm import Session, selectinload
from yg_crm import schemas, crud, orm
from typing import List, Optional, Annotated, Dict
from utils.utility import result, RespResult, json_result_reps
from utils.myJsonHandler import orjson_dumps
from utils.log_util import logger as log
from config.env import DataBaseConfig as db_conf
from sqlalchemy.ext.asyncio import AsyncSession

from yg_crm.schemas.sys import JsonSettingModel

sys_router = APIRouter()


@sys_router.post("/getDataBySql")
def getDataBySql(db: Session = Depends(get_db), isUpdate: Optional[bool] = Body(embed=True, default=False), groupedBy: Optional[str] = Body(embed=True, default=None), sql: str = Body(embed=True,
                                                                examples=[
                                                                    {
                                                                        "sql": "select name from user",
                                                                    }
                                                                ]
                                                                )):

    def replace_str_with_quotes(txt, pattern):
        def replacer(match):
            #print("|" + match.group(0) + "|")
            return f' {match.group(1)}' if "_"  in match.group(1) else f' "{match.group(1)}"'

        pattern = re.compile(pattern, re.IGNORECASE)
        return pattern.sub(replacer, txt, count=1)

    # sql兼容性处理
    if 'postgresql' in db_conf.db_type:
        pattern_table = "(?:(?<=update)|(?<=from)|(?<=join))\s+([\w\u4e00-\u9fa5_]+)(?=[\s\,]?)"
        sql = replace_str_with_quotes(sql, pattern_table)

    log.info("isUpdate:{}", isUpdate)
    log.info("sql{}", sql)
    data = crud.getDataBySql(db, sql, isUpdate, groupedBy)

    return json_result_reps(data=data,use_serializer=True)


@sys_router.post("/asyncDataBySql")
async def asyncDataBySql(db: AsyncSession = Depends(get_db_async), isUpdate: Optional[bool] = Body(embed=True, default=False), sqls: List[str] = Body(embed=True,
                                                                examples=[
                                                                    {
                                                                        "sqls":[ "select name from user" ],
                                                                    }
                                                                ]
                                                                )):

    def replace_str_with_quotes(txt, pattern):
        def replacer(match):
            #print("|" + match.group(0) + "|")
            return f' {match.group(1)}' if "_"  in match.group(1) else f' "{match.group(1)}"'

        pattern = re.compile(pattern, re.IGNORECASE)
        return pattern.sub(replacer, txt, count=1)

    # sql兼容性处理
    if 'postgresql' in db_conf.db_type:
        for sql in sqls:
            pattern_table = "(?:(?<=update)|(?<=from)|(?<=join))\s+([\w\u4e00-\u9fa5_]+)(?=[\s\,]?)"
            sql = replace_str_with_quotes(sql, pattern_table)

    log.info("isUpdate:{}", isUpdate)
    log.info("sql{}", sqls)
    data = await crud.execute_async_sql(db, sqls, isUpdate)
    print(data)
    return json_result_reps(data=data,use_serializer=True)


@sys_router.get("/pdfPreview")
def pdf_preview(filePath: str):
    print(filePath)
    params = {"file": f'/oss/read?filePath={filePath}'}
    encoded_params = urlencode(params)
    return RedirectResponse(url=f"/preview/viewer.html?{encoded_params}")


"""
jsonSetting
"""
@sys_router.get("/jsonSetting")
async def getJsonSetting(db: AsyncSession = Depends(get_db_async), key: str = None, type: str = None):
    sys_json_setting = (
        (await db.execute(select(orm.SysJsonSettings).where(orm.SysJsonSettings.key == key,
                                                            orm.SysJsonSettings.type == type))).scalars().first()
    )
    res = []
    if sys_json_setting:
        # await db.refresh(sys_json_setting)  # 显示执行select, 数据库数据同步到内存
        res = orjson.loads(sys_json_setting.value)
    return json_result_reps(data=res)


@sys_router.delete("/jsonSetting")
async def deleteJsonSetting(db: AsyncSession = Depends(get_db_async), key: str = None, type: str = None):
    await db.execute(delete(orm.SysJsonSettings).where(orm.SysJsonSettings.key == key,
                                                       orm.SysJsonSettings.type == type))
    await db.commit()
    return json_result_reps(message="删除成功")


@sys_router.post("/jsonSetting")
async def saveJsonSetting(db: AsyncSession = Depends(get_db_async), jsonSetting: JsonSettingModel = Body()):
    sys_json_setting = (
        (await db.execute(select(orm.SysJsonSettings).where(orm.SysJsonSettings.key == jsonSetting.key,
                                                            orm.SysJsonSettings.type == jsonSetting.type))).scalars().first()
    )
    if sys_json_setting:
        sys_json_setting.value = jsonSetting.value
        sys_json_setting.update_time = datetime.now()
        Id = sys_json_setting.id
    else:
        sys_json_setting = orm.SysJsonSettings(**jsonSetting.model_dump())
        sys_json_setting.create_time = datetime.now()
        sys_json_setting.create_by = jsonSetting.update_by
        db.add(sys_json_setting)
        await db.flush() # 执行 insert,update,delete,同步新的id
        Id = sys_json_setting.id
    await db.commit()
    # print(jsonSetting)
    return json_result_reps(data={"id": Id})